package com.viettel.pon.common;

import java.io.File;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Locale;

import org.apache.log4j.Logger;

import android.R.string;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.CursorJoiner.Result;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.os.Environment;
import android.util.Log;
import android.widget.Toast;

import com.viettel.pon.form.InfomationForm;

public class DataBaseHelper extends SQLiteOpenHelper {
	
	
	private Logger mLog;
	/*
	 * Database Version
	 */private static final int DATABASE_VERSION = 2;

	/*
	 * Database Name
	 */private static final String DATABASE_NAME = "AppPONDB.sqlite";

	// Table Names
	private static final String POLES_LIST = "POLES_LIST";
	private static final String TIME_STOP_LIST = "LIST_TIME_STOP";
	private static final String POSITION_LIST = "POSITION_LIST";
	private static final String HOUSE_LIST = "HOUSE_LIST";
	private static final String PLACE_LIST = "PLACE_LIST";
	private static final String PROJECT_NAME = "PROJECT";

	// Common column names table INFO_COL
	private static final String KEY_ID = "id";
	private static final String LAT = "latitude";
	private static final String LONG = "longtitude";
	private static final String COL_CODE = "col_code";
	private static final String COL_TYPE = "col_type";
	private static final String OWNER = "owner";
	private static final String POSITION = "position";
	
	// private static final String POSITION = "position";
	private static final String NUM_PEOPLE = "num_people";
	private static final String NOTE = "note";
	private static final String USERNAME = "username";
	private static final String NEWOROLD = "neworold";
	private static final String CREATETIME = "createtime";

	// Common column names table TIME_STOP_LIST
	private static final String TOTAL_TIME = "total_time";
	private static final String TOTAL_LONG = "total_long";
	private static final String TRACK_NAME = "track_name";
	private static final String TRACK_NOTE = "track_note";

	// Common column names table POSITION_LIST
	private static final String TIME_STOP_ID = "time_stop_id";

	// Common column names table PLACE_LIST
	private static final String PLACE_NAME = "place_name";
	private static final String PLACE_NOTE = "place_note";

	private static final String CREATE_TABLE_PROJECT = "CREATE TABLE PROJECT "
			+ "( ID INTEGER PRIMARY KEY AUTOINCREMENT,"
			+ " NAME TEXT, DESCRIPTION TEXT)";

	// Create table INFO_COL
	private static final String CREATE_TABLE_POLES = "CREATE TABLE "
			+ POLES_LIST + "(" + KEY_ID + " INTEGER PRIMARY KEY AUTOINCREMENT,"
			+ COL_CODE + " TEXT," + LAT + " TEXT," + LONG + " TEXT," + COL_TYPE
			+ " INTEGER," + OWNER + " INTEGER," + NEWOROLD + " INTEGER,"
			+ POSITION + " INTEGER," + NUM_PEOPLE + " TEXT," + NOTE
			+ " TEXT," + USERNAME + " TEXT," + " deviceHight TEXT, devicePosition TEXT," + CREATETIME
			+ " DATETIME DEFAULT CURRENT_TIMESTAMP," + TIME_STOP_ID
			+ " INTEGER, COMMITED INTEGER)";

	// Create table list stop time
	private static final String CREATE_TABLE_TIME_STOP_LIST = "CREATE TABLE "
			+ TIME_STOP_LIST + "(" + KEY_ID
			+ " INTEGER PRIMARY KEY AUTOINCREMENT," + CREATETIME
			+ "  DATETIME DEFAULT CURRENT_TIMESTAMP," + TOTAL_TIME + " TEXT,"
			+ TOTAL_LONG + " TEXT," + TRACK_NAME + " TEXT," + TRACK_NOTE
			+ " TEXT, COMMITED INTEGER)";

	// Create table list position
	private static final String CREATE_TABLE_POSITION_LIST = "CREATE TABLE "
			+ POSITION_LIST + "(" + KEY_ID
			+ " INTEGER PRIMARY KEY AUTOINCREMENT," + LAT + " TEXT," + LONG
			+ " TEXT," + TIME_STOP_ID + " INTEGER," + CREATETIME
			+ " DATETIME DEFAULT CURRENT_TIMESTAMP, COMMITED INTEGER)";

	// Create table house list
	private static final String CREATE_TABLE_HOUSE_LIST = "CREATE TABLE "
			+ HOUSE_LIST + "(" + KEY_ID + " INTEGER PRIMARY KEY AUTOINCREMENT,"
			+ LAT + " TEXT," + LONG + " TEXT," + POSITION + " INTEGER,"
			+ CREATETIME + " DATETIME DEFAULT CURRENT_TIMESTAMP,"
			+ TIME_STOP_ID + " INTEGER, COMMITED INTEGER)";

	// Create table place list
	private static final String CREATE_TABLE_PLACE_LIST = "CREATE TABLE "
			+ PLACE_LIST + "(" + KEY_ID + " INTEGER PRIMARY KEY AUTOINCREMENT,"
			+ LAT + " TEXT," + LONG + " TEXT," + PLACE_NAME + " TEXT,"
			+ PLACE_NOTE + " TEXT," + CREATETIME
			+ " DATETIME DEFAULT CURRENT_TIMESTAMP," + TIME_STOP_ID
			+ " INTEGER, COMMITED INTEGER)";

	public DataBaseHelper(Context context) {
		 //super(context, "/mnt/sdcard/"+DATABASE_NAME, null, DATABASE_VERSION);
//		 super(context, Environment.getExternalStorageDirectory()
//		 + File.separator + "dbPON" + File.separator + DATABASE_NAME,
//					null, DATABASE_VERSION);
		super(context, DATABASE_NAME, null, DATABASE_VERSION);
		 SQLiteDatabase db = this.getWritableDatabase();
		mLog = Logger.getLogger(DataBaseHelper.class);
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		// creating required tables
		db.execSQL(CREATE_TABLE_POLES);
		db.execSQL(CREATE_TABLE_TIME_STOP_LIST);
		db.execSQL(CREATE_TABLE_POSITION_LIST);
		db.execSQL(CREATE_TABLE_HOUSE_LIST);
		db.execSQL(CREATE_TABLE_PLACE_LIST);
		db.execSQL(CREATE_TABLE_PROJECT);
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		// TODO Auto-generated method stub
		db.execSQL("DROP TABLE IF EXISTS " + POLES_LIST);
		db.execSQL("DROP TABLE IF EXISTS " + TIME_STOP_LIST);
		db.execSQL("DROP TABLE IF EXISTS " + POSITION_LIST);
		db.execSQL("DROP TABLE IF EXISTS " + HOUSE_LIST);
		db.execSQL("DROP TABLE IF EXISTS " + PLACE_LIST);
		db.execSQL("DROP TABLE IF EXISTS PROJECT");
		onCreate(db);
	}

	// return -1 if fail
	public long insertRoad(String totalTime, String totalLong,
			String trackName, String trackNote) {
		SQLiteDatabase db = this.getWritableDatabase();
		long val = -1;
		try {
			ContentValues values = new ContentValues();
			values.put(CREATETIME, getDateTime());
			values.put(TOTAL_TIME, totalTime);
			values.put(TOTAL_LONG, totalLong);
			values.put(TRACK_NAME, trackName);
			values.put(TRACK_NOTE, trackNote);
			// insert row
			val = db.insert(TIME_STOP_LIST, null, values);
			Log.e("Result + Insert" + TIME_STOP_LIST, "Success");
			mLog.info(TIME_STOP_LIST + " insertRoad Success");
			db.close();
		} catch (Exception e) {
			e.printStackTrace();
			Log.e("Result + Insert" + TIME_STOP_LIST, "Fail");
			mLog.error(TIME_STOP_LIST + " insertRoad fail", e);
		}
		return val;
	}

	public long dupplicateExistRoad(int roadID) {
		long val = -1;
		String selectQuery = "SELECT * FROM " + TIME_STOP_LIST + " where id="
				+ roadID;
		try {
			SQLiteDatabase db = this.getReadableDatabase();
			// get road infomation
			Cursor c = db.rawQuery(selectQuery, null);
			InfomationForm info = new InfomationForm();
			if (c.moveToFirst()) {
				do {
					info.setId(c.getInt(c.getColumnIndex(KEY_ID)));
					info.setCreateDate(c.getString(c.getColumnIndex(CREATETIME)));
					info.setTotalLong(c.getString(c.getColumnIndex(TOTAL_LONG)));
					info.setTotalTime(c.getString(c.getColumnIndex(TOTAL_TIME)));
					info.setTrackName(c.getString(c.getColumnIndex(TRACK_NAME)));
					info.setTrackNote(c.getString(c.getColumnIndex(TRACK_NOTE)));
					break;
				} while (c.moveToNext());
			} else {
				return -1;
			}
			c.close();
			// insert new road
			val = insertRoad(info.getTotalTime(), info.getTotalLong(),
					info.getTrackName(), info.getTrackNote());
		} catch (Exception e) {
			e.printStackTrace();
		}

		return val;
	}

	public long dupplicateExistRoad(int roadID, Context context) {
		long val = -1;
		String selectQuery = "SELECT * FROM " + TIME_STOP_LIST + " where id="
				+ roadID;
		try {
			Toast.makeText(context, "start insert, ID: " + roadID,
					Toast.LENGTH_SHORT).show();
			SQLiteDatabase db = this.getReadableDatabase();
			// get road infomation
			Cursor c = db.rawQuery(selectQuery, null);
			InfomationForm info = new InfomationForm();
			if (c.moveToFirst()) {
				do {
					info.setId(c.getInt(c.getColumnIndex(KEY_ID)));
					info.setCreateDate(c.getString(c.getColumnIndex(CREATETIME)));
					info.setTotalLong(c.getString(c.getColumnIndex(TOTAL_LONG)));
					info.setTotalTime(c.getString(c.getColumnIndex(TOTAL_TIME)));
					info.setTrackName(c.getString(c.getColumnIndex(TRACK_NAME)));
					info.setTrackNote(c.getString(c.getColumnIndex(TRACK_NOTE)));
					Toast.makeText(context,"create time"
									+ c.getString(c.getColumnIndex(CREATETIME)),
							Toast.LENGTH_SHORT).show();
					break;
				} while (c.moveToNext());
			} else {
				return -1;
			}
			c.close();
			// insert new road
			val = insertRoad(info.getTotalTime(), info.getTotalLong(),
					info.getTrackName(), info.getTrackNote());
		} catch (Exception e) {
			e.printStackTrace();
		}

		return val;
	}

	private String getDateTime() {
		SimpleDateFormat dateFormat = new SimpleDateFormat(
				"yyyy-MM-dd HH:mm:ss", Locale.getDefault());
		Date date = new Date();
		return dateFormat.format(date);
	}

	// ------------------------ "LookUpLink" table methods ----------------//

	/*
	 * hungnd40_start Them moi dia danh
	 */

	public int insertPlaceList(String lattuti, String longtuti, String name,
			String note, int timeListId) {
		int idMax = -1;
		SQLiteDatabase db = this.getWritableDatabase();
		try {
			ContentValues values = new ContentValues();
			values.put(LAT, lattuti);
			values.put(LONG, longtuti);
			values.put(PLACE_NAME, name);
			values.put(PLACE_NOTE, note);
			values.put(CREATETIME, getDateTime());
			values.put(TIME_STOP_ID, timeListId);
			// insert row
			db.insert(PLACE_LIST, null, values);
			Log.e("Result + Insert" + PLACE_LIST, "Success");
			mLog.info(PLACE_LIST + " insertPlaceList Success");
			// db.close();
		} catch (Exception e) {
			e.printStackTrace();
			Log.e("Result + Insert" + PLACE_LIST, "Fail");
			mLog.error(PLACE_LIST + " insertPlaceList fail", e);
			return idMax;
		}
		Cursor cursor = db.rawQuery("SELECT MAX(id) FROM PLACE_LIST", null);
		if (cursor.moveToFirst())
			do {
				idMax = cursor.getInt((0));
			} while (cursor.moveToNext());
		db.close();
		return idMax;
	}

	/*
	 * hungnd40_start Them moi danh sach nha
	 */

	public int insertHouseList(String lattuti, String longtuti, int timeListId,
			int position) {
		int idMax = -1;
		SQLiteDatabase db = this.getWritableDatabase();
		try {
			ContentValues values = new ContentValues();
			values.put(LAT, lattuti);
			values.put(LONG, longtuti);
			values.put(CREATETIME, getDateTime());
			values.put(TIME_STOP_ID, timeListId);
			values.put(POSITION, position);
			// insert row
			db.insert(HOUSE_LIST, null, values);
			Log.e("Result + Insert" + HOUSE_LIST, "Success");
			mLog.info(HOUSE_LIST + " insertHouseList Success");
			// db.close();
		} catch (Exception e) {
			e.printStackTrace();
			Log.e("Result + Insert" + HOUSE_LIST, "Fail");
			mLog.error(HOUSE_LIST + " insertHouseList fail", e);
			return idMax;
		}
		Cursor cursor = db.rawQuery("SELECT MAX(id) FROM HOUSE_LIST", null);
		if (cursor.moveToFirst())
			do {
				idMax = cursor.getInt((0));
			} while (cursor.moveToNext());
		db.close();
		return idMax;
	}

	/*
	 * hungnd40_start Them moi cac toa do duong di
	 */
	public int insertPositionList(String lattuti, String longtuti,
			int timeStopId) {
		SQLiteDatabase db = this.getWritableDatabase();
		try {
			ContentValues values = new ContentValues();
			values.put(LAT, lattuti);
			values.put(LONG, longtuti);
			values.put(TIME_STOP_ID, timeStopId);
			values.put(CREATETIME, getDateTime());
			// insert row
			db.insert(POSITION_LIST, null, values);
			Log.e("Result + Insert" + POSITION_LIST, "Success");
			mLog.info(POSITION_LIST + " insertPositionList Success");
			db.close();
		} catch (Exception e) {
			e.printStackTrace();
			Log.e("Result + Insert" + POSITION_LIST, "Fail");
			mLog.error(POSITION_LIST + " insertPositionList fail", e);
			return 1;
		}
		return 0;
	}

	public int getRoadPointCount(int roadID, Context context) {
		int val = -1;
		String selectQuery = "SELECT count * FROM " + POSITION_LIST;
		SQLiteDatabase db = this.getWritableDatabase();
		try {
			Cursor c = db.rawQuery(selectQuery, null);
			val = c.getInt(1);
			Log.e("Result + Insert" + TIME_STOP_LIST, "Success");
			db.close();
			Toast.makeText(context, "point count: " + val, Toast.LENGTH_SHORT)
					.show();
		} catch (Exception e) {
			e.printStackTrace();
			Log.e("Result get Road Point count", "Fail");
			return -1;
		}
		return val;
	}

	/*
	 * hungnd40_start Them moi track
	 */

	public int insertTimeStopList(String totalTime, String totalLong,
			String trackName, String trackNote) {
		SQLiteDatabase db = this.getWritableDatabase();
		try {
			ContentValues values = new ContentValues();
			values.put(CREATETIME, getDateTime());
			values.put(TOTAL_TIME, totalTime);
			values.put(TOTAL_LONG, totalLong);
			values.put(TRACK_NAME, trackName);
			values.put(TRACK_NOTE, trackNote);
			// insert row
			db.insert(TIME_STOP_LIST, null, values);
			Log.e("Result + Insert" + TIME_STOP_LIST, "Success");
			mLog.info(TIME_STOP_LIST + " insertTimeStopList Success");
			db.close();
		} catch (Exception e) {
			e.printStackTrace();
			Log.e("Result + Insert" + TIME_STOP_LIST, "Fail");
			mLog.error(TIME_STOP_LIST + " insertTimeStopList fail", e);
			return 1;
		}
		return 0;
	}

	/*
	 * hungnd40_start Them moi cot dien
	 */

	public int insertInfoCol(InfomationForm infoCol) {
		SQLiteDatabase db = this.getWritableDatabase();
		int idMax = -1;
		try {
			ContentValues values = new ContentValues();
			// values.put("id", infoCol.getId());
			values.put("latitude", infoCol.getLatitude());
			values.put("longtitude", infoCol.getLongtitude());
			values.put("col_code", infoCol.getCol_code());
			values.put("col_type", infoCol.getCol_type());
			values.put("owner", infoCol.getOwner());
			values.put("position", infoCol.getPosition());
			values.put("num_people", infoCol.getNum_people());
			values.put("note", infoCol.getNote());
			values.put("neworold", infoCol.getNeworold());
			values.put("username", "null");
			values.put("createtime", getDateTime());
			values.put(TIME_STOP_ID, infoCol.getTimeListId());
			values.put("deviceHight", infoCol.getDoCaoThietBi());
			values.put("devicePosition", infoCol.getViTriThietBi());
			// insert row
			db.insert(POLES_LIST, null, values);
			Log.e("Result + Insert", "Success");
			mLog.info(POLES_LIST + " insertInfoCol Success");
			// db.close();
		} catch (Exception e) {
			e.printStackTrace();
			mLog.error(POLES_LIST + " insertInfoCol fail", e);
			return idMax;
		}
		Cursor cursor = db.rawQuery("SELECT MAX(id) FROM POLES_LIST", null);
		if (cursor.moveToFirst())
			do {
				idMax = cursor.getInt((0));
			} while (cursor.moveToNext());
		db.close();
		return idMax;
	}

	/*
	 * hungnd40_start Cap nhat cot dien
	 */

	public int updateInfoCol(InfomationForm infoCol) {
		SQLiteDatabase db = this.getWritableDatabase();
		try {
			ContentValues values = new ContentValues();
			values.put("latitude", infoCol.getLatitude());
			values.put("longtitude", infoCol.getLongtitude());
			values.put("col_code", infoCol.getCol_code());
			values.put("col_type", infoCol.getCol_type());
			values.put("owner", infoCol.getOwner());
			values.put("position", infoCol.getPosition());
			values.put("num_people", infoCol.getNum_people());
			values.put("note", infoCol.getNote());
			values.put("neworold", infoCol.getNeworold());
			values.put("username", "null");
			values.put("createtime", getDateTime());
			values.put("deviceHight", infoCol.getDoCaoThietBi());
			values.put("devicePosition", infoCol.getViTriThietBi());
			values.put("COMMITED", infoCol.getCommit());
			// insert row
			db.update(POLES_LIST, values, KEY_ID + " = ?",
					new String[] { String.valueOf(infoCol.getId()) });
			Log.e("Result + Insert", "Success");
			mLog.info(POLES_LIST + " updateInfoCol Success");
			db.close();
		} catch (Exception e) {
			e.printStackTrace();
			mLog.error(POLES_LIST + " updateInfoCol fail", e);
			return 1;
		}
		return 0;
	}

	/*
	 * hungnd40_start Lay het cac cot dien
	 */

	public ArrayList<InfomationForm> getAllCol() {
		String selectQuery = "SELECT * FROM " + POLES_LIST
				+ " order by createtime desc";
		ArrayList<InfomationForm> lstCol = new ArrayList<InfomationForm>();
		try {
			SQLiteDatabase db = this.getReadableDatabase();
			Cursor c = db.rawQuery(selectQuery, null);
			if (c.moveToFirst()) {
				do {
					InfomationForm info = new InfomationForm();
					info.setId(c.getInt(c.getColumnIndex("id")));
					info.setCol_code(c.getString(c.getColumnIndex("col_code")));
					info.setLatitude(c.getString(c.getColumnIndex("latitude")));
					info.setLongtitude(c.getString(c
							.getColumnIndex("longtitude")));
					info.setCol_type(c.getInt(c.getColumnIndex("col_type")));
					info.setOwner(c.getInt(c.getColumnIndex("owner")));
					info.setNum_people(c.getString(c.getColumnIndex("num_people")));
					info.setNeworold(c.getInt(c.getColumnIndex("neworold")));
					info.setNote(c.getString(c.getColumnIndex("note")));
					info.setUsername(c.getString(c.getColumnIndex("username")));
					info.setViTriThietBi(c.getString(c.getColumnIndex("devicePosition")));
					info.setDoCaoThietBi(c.getString(c.getColumnIndex("deviceHight")));
					info.setCommit(c.getString(c.getColumnIndex("COMMITED")));
					lstCol.add(info);
				} while (c.moveToNext());
			}
			mLog.info(POLES_LIST + " getAllCol Success");
			c.close();
		} catch (Exception e) {
			e.printStackTrace();
			mLog.error(POLES_LIST + " getAllCol fail", e);
		}

		return lstCol;
	}

	/*
	 * hungnd40_start lay cac cot chua insert
	 */

	public ArrayList<InfomationForm> getPoleNotInserted() {

		ArrayList<InfomationForm> lstCol = new ArrayList<InfomationForm>();
		try {
			SQLiteDatabase db = this.getReadableDatabase();
			Cursor c = db.query(POLES_LIST, new String[] { KEY_ID, LAT, LONG,
					COL_CODE, COL_TYPE, OWNER, NUM_PEOPLE, NEWOROLD, NOTE,
					USERNAME, CREATETIME, "devicePosition", "deviceHight" },
					"COMMITED is NULL",
					null, null, null, CREATETIME + " DESC", null);
			
			if (c.moveToFirst()) {
				do {
					InfomationForm info = new InfomationForm();
					info.setId(c.getInt(c.getColumnIndex(KEY_ID)));
					info.setCol_code(c.getString(c.getColumnIndex("col_code")));
					info.setLatitude(c.getString(c.getColumnIndex(LAT)));
					info.setLongtitude(c.getString(c.getColumnIndex(LONG)));
					info.setCol_type(c.getInt(c.getColumnIndex(COL_TYPE)));
					info.setOwner(c.getInt(c.getColumnIndex(OWNER)));
					info.setNum_people(c.getString(c.getColumnIndex(NUM_PEOPLE)));
					info.setNeworold(c.getInt(c.getColumnIndex(NEWOROLD)));
					info.setNote(c.getString(c.getColumnIndex(NOTE)));
					info.setUsername(c.getString(c.getColumnIndex(USERNAME)));
					info.setViTriThietBi(c.getString(c.getColumnIndex("devicePosition")));
					info.setDoCaoThietBi(c.getString(c.getColumnIndex("deviceHight")));
					lstCol.add(info);
				} while (c.moveToNext());
			}
			mLog.info(POLES_LIST + " getAllCol Success");
			c.close();
		} catch (Exception e) {
			e.printStackTrace();
			mLog.error(POLES_LIST + " getAllCol fail", e);
		}

		return lstCol;
	}

	
	
	/*
	 * Get
	 */
	

	public List<InfomationForm> getPoleByDate(java.util.Date date) {
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
		String to_date = sdf.format(date);

		List<InfomationForm> lstCol = new ArrayList<InfomationForm>();
		try {
			SQLiteDatabase db = this.getReadableDatabase();

			Cursor c = db.query(POLES_LIST, new String[] { KEY_ID, LAT, LONG,
					COL_CODE, COL_TYPE, OWNER, NUM_PEOPLE, NEWOROLD, NOTE,
					USERNAME, "devicePosition", "deviceHight" }, "date(CREATETIME) = ?",
					new String[] { to_date }, null, null, KEY_ID, null);
			if (c.moveToFirst()) {
				do {
					InfomationForm info = new InfomationForm();

					info.setId(c.getInt(c.getColumnIndex("id")));
					info.setCol_code(c.getString(c.getColumnIndex("col_code")));
					info.setLatitude(c.getString(c.getColumnIndex("latitude")));
					info.setLongtitude(c.getString(c
							.getColumnIndex("longtitude")));
					info.setCol_type(c.getInt(c.getColumnIndex("col_type")));
					info.setOwner(c.getInt(c.getColumnIndex("owner")));
					info.setNum_people(c.getString(c.getColumnIndex("num_people")));
					info.setNeworold(c.getInt(c.getColumnIndex("neworold")));
					info.setNote(c.getString(c.getColumnIndex("note")));
					info.setUsername(c.getString(c.getColumnIndex("username")));
					info.setViTriThietBi(c.getString(c.getColumnIndex("devicePosition")));
					info.setDoCaoThietBi(c.getString(c.getColumnIndex("deviceHight")));

					lstCol.add(info);
				} while (c.moveToNext());
			}
			mLog.info(POLES_LIST + " getPoleByTime Success");
			c.close();
			db.close();
		} catch (Exception e) {
			e.printStackTrace();
			mLog.error(POLES_LIST + " getHouseByTime fail", e);
		}
		return lstCol;
	}

	/*
	 * hungnd40_start Lay thong tin cot de update
	 */

	public List<InfomationForm> getAllColUpdate(int colId) {
		String selectQuery = "SELECT * FROM " + POLES_LIST + " where id <> "
				+ colId + " order by col_code";
		List<InfomationForm> lstCol = new ArrayList<InfomationForm>();
		try {
			SQLiteDatabase db = this.getReadableDatabase();
			Cursor c = db.rawQuery(selectQuery, null);
			if (c.moveToFirst()) {
				do {
					InfomationForm info = new InfomationForm();
					info.setId(c.getInt(c.getColumnIndex("id")));
					info.setCol_code(c.getString(c.getColumnIndex("col_code")));
					info.setLatitude(c.getString(c.getColumnIndex("latitude")));
					info.setLongtitude(c.getString(c
							.getColumnIndex("longtitude")));
					info.setCol_type(c.getInt(c.getColumnIndex("col_type")));
					info.setOwner(c.getInt(c.getColumnIndex("owner")));
					// info.setPosition(c.getInt(c.getColumnIndex("position")));
					info.setNum_people(c.getString(c.getColumnIndex("num_people")));
					info.setNeworold(c.getInt(c.getColumnIndex("neworold")));
					info.setNote(c.getString(c.getColumnIndex("note")));
					info.setUsername(c.getString(c.getColumnIndex("username")));
					info.setViTriThietBi(c.getString(c.getColumnIndex("devicePosition")));
					info.setDoCaoThietBi(c.getString(c.getColumnIndex("deviceHight")));
					info.setCommit(c.getString(c.getColumnIndex("COMMITED")));
					lstCol.add(info);
				} while (c.moveToNext());
			}
			mLog.info(POLES_LIST + " getAllColUpdate Success");
			c.close();
			db.close();
		} catch (Exception e) {
			e.printStackTrace();
			mLog.error(POLES_LIST + " getAllColUpdate fail", e);
		}

		return lstCol;
	}

	/*
	 * hungnd40_start Lay cot theo ma cot dien
	 */

	public ArrayList<InfomationForm> getColByCode(String colCode) {
		ArrayList<InfomationForm> lstCol = new ArrayList<InfomationForm>();
		try {
			SQLiteDatabase db = this.getReadableDatabase();
			Cursor c = db.query(
					POLES_LIST,
					new String[] { KEY_ID, COL_CODE, LAT, LONG, COL_TYPE,
							OWNER, POSITION, NUM_PEOPLE, NEWOROLD, NOTE,
							USERNAME, "devicePosition", "deviceHight", "COMMITED" },
					COL_CODE + " LIKE ? ESCAPE '\\' ",
					new String[] { "%"
							+ colCode.replace("\\", "\\\\")
									.replaceAll("%", "\\\\%")
									.replaceAll("_", "\\\\_") + "%" }, null,
					null, COL_CODE, null);
			if (c.moveToFirst()) {
				do {
					InfomationForm info = new InfomationForm();
					info.setId(c.getInt(c.getColumnIndex("id")));
					info.setCol_code(c.getString(c.getColumnIndex("col_code")));
					info.setLatitude(c.getString(c.getColumnIndex("latitude")));
					info.setLongtitude(c.getString(c
							.getColumnIndex("longtitude")));
					info.setCol_type(c.getInt(c.getColumnIndex("col_type")));
					info.setOwner(c.getInt(c.getColumnIndex("owner")));
					info.setPosition(c.getInt(c.getColumnIndex("position")));
					info.setNum_people(c.getString(c.getColumnIndex("num_people")));
					info.setNeworold(c.getInt(c.getColumnIndex("neworold")));
					info.setNote(c.getString(c.getColumnIndex("note")));
					info.setUsername(c.getString(c.getColumnIndex("username")));
					info.setViTriThietBi(c.getString(c.getColumnIndex("devicePosition")));
					info.setDoCaoThietBi(c.getString(c.getColumnIndex("deviceHight")));
					info.setCommit(c.getString(c.getColumnIndex("COMMITED")));
					lstCol.add(info);
				} while (c.moveToNext());
			}
			mLog.info(POLES_LIST + " getColByCode Success");
			c.close();
			db.close();
		} catch (Exception e) {
			e.printStackTrace();
			mLog.error(POLES_LIST + " getColByCode fail", e);
		}
		return lstCol;
	}

	/*
	 * hungnd40_start Lay cot theo id
	 */

	public List<InfomationForm> getColById(int colId) {
		List<InfomationForm> lstCol = new ArrayList<InfomationForm>();
		try {
			SQLiteDatabase db = this.getReadableDatabase();
			Cursor c = db.query(POLES_LIST, new String[] { KEY_ID, COL_CODE,
					LAT, LONG, COL_TYPE, OWNER, POSITION, NUM_PEOPLE, NEWOROLD,
					NOTE, USERNAME, "devicePosition", "deviceHight", "COMMITED"}, KEY_ID + " = ? ",
					new String[] { String.valueOf(colId) }, null, null,
					COL_CODE, null);
			if (c.moveToFirst()) {
				do {
					InfomationForm info = new InfomationForm();
					info.setId(c.getInt(c.getColumnIndex("id")));
					info.setCol_code(c.getString(c.getColumnIndex("col_code")));
					info.setLatitude(c.getString(c.getColumnIndex("latitude")));
					info.setLongtitude(c.getString(c.getColumnIndex("longtitude")));
					info.setCol_type(c.getInt(c.getColumnIndex("col_type")));
					info.setOwner(c.getInt(c.getColumnIndex("owner")));
					info.setPosition(c.getInt(c.getColumnIndex("position")));
					info.setNum_people(c.getString(c.getColumnIndex("num_people")));
					info.setNeworold(c.getInt(c.getColumnIndex("neworold")));
					info.setNote(c.getString(c.getColumnIndex("note")));
					info.setUsername(c.getString(c.getColumnIndex("username")));
					info.setViTriThietBi(c.getString(c.getColumnIndex("devicePosition")));
					info.setDoCaoThietBi(c.getString(c.getColumnIndex("deviceHight")));
					info.setCommit(c.getString(c.getColumnIndex("COMMITED")));
					lstCol.add(info);
				} while (c.moveToNext());
			}
			mLog.info(POLES_LIST + " getColById Success");
			c.close();
			db.close();
		} catch (Exception e) {
			e.printStackTrace();
			mLog.error(POLES_LIST + " getColById fail", e);
		}
		return lstCol;
	}

	/*
	 * hungnd40_start Lay het cac track
	 */

	public ArrayList<InfomationForm> getAllTimeStop() {
		ArrayList<InfomationForm> lstTime = new ArrayList<InfomationForm>();
		try {
			SQLiteDatabase db = this.getReadableDatabase();
			Cursor c = db.query(TIME_STOP_LIST,
					new String[] { KEY_ID, CREATETIME, TOTAL_TIME, TOTAL_LONG,
							TRACK_NAME, TRACK_NOTE }, null, null, null, null,
					CREATETIME + " desc", null);
			// looping through all rows and adding to list
			if (c.moveToFirst()) {
				do {
					InfomationForm info = new InfomationForm();
					info.setId(c.getInt(c.getColumnIndex(KEY_ID)));
					info.setCreateDate(c.getString(c.getColumnIndex(CREATETIME)));
					info.setTotalLong(c.getString(c.getColumnIndex(TOTAL_LONG)));
					info.setTotalTime(c.getString(c.getColumnIndex(TOTAL_TIME)));
					info.setTrackName(c.getString(c.getColumnIndex(TRACK_NAME)));
					info.setTrackNote(c.getString(c.getColumnIndex(TRACK_NOTE)));
					lstTime.add(info);
				} while (c.moveToNext());
			}
			mLog.info(TIME_STOP_LIST + " getAllTimeStop Success");
			c.close();
		} catch (Exception e) {
			e.printStackTrace();
			mLog.error(TIME_STOP_LIST + " getAllTimeStop fail", e);
		}
		return lstTime;
	}
	
	/*
	 * Get TimeStop by Date
	 */
	
	public ArrayList<InfomationForm> getTimeStopByDate(java.util.Date date) {
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        String to_date = sdf.format(date);
        
		ArrayList<InfomationForm> lstTime = new ArrayList<InfomationForm>();
		try {
			SQLiteDatabase db = this.getReadableDatabase();
			Cursor c = db.query(TIME_STOP_LIST,
					new String[] { KEY_ID, CREATETIME, TOTAL_TIME, TOTAL_LONG,
							TRACK_NAME, TRACK_NOTE }, "date(CREATETIME) = ?", new String[] { to_date  }, null, null,
					CREATETIME + " desc", null);
			// looping through all rows and adding to list
			if (c.moveToFirst()) {
				do {
					InfomationForm info = new InfomationForm();
					info.setId(c.getInt(c.getColumnIndex(KEY_ID)));
					info.setCreateDate(c.getString(c.getColumnIndex(CREATETIME)));
					info.setTotalLong(c.getString(c.getColumnIndex(TOTAL_LONG)));
					info.setTotalTime(c.getString(c.getColumnIndex(TOTAL_TIME)));
					info.setTrackName(c.getString(c.getColumnIndex(TRACK_NAME)));
					info.setTrackNote(c.getString(c.getColumnIndex(TRACK_NOTE)));
					lstTime.add(info);
				} while (c.moveToNext());
			}
			mLog.info(TIME_STOP_LIST + " getAllTimeStop Success");
			c.close();
		} catch (Exception e) {
			e.printStackTrace();
			mLog.error(TIME_STOP_LIST + " getAllTimeStop fail", e);
		}
		return lstTime;
	}

	public ArrayList<InfomationForm> getRoadNotInserted() {
		ArrayList<InfomationForm> lstTime = new ArrayList<InfomationForm>();
		try {
			SQLiteDatabase db = this.getReadableDatabase();
			//hungnd40_comment_
			
			Cursor c = db.query(TIME_STOP_LIST,
					new String[] { KEY_ID, CREATETIME, TOTAL_TIME, TOTAL_LONG,
							TRACK_NAME, TRACK_NOTE }, 
							" COMMITED is NULL"
							, null,
//					TRACK_NAME, TRACK_NOTE }, null, null,
					null, null, CREATETIME + " DESC", null);
			// looping through all rows and adding to list
			if (c.moveToFirst()) {
				do {
					InfomationForm info = new InfomationForm();
					info.setId(c.getInt(c.getColumnIndex(KEY_ID)));
					info.setCreateDate(c.getString(c.getColumnIndex(CREATETIME)));
					info.setTotalLong(c.getString(c.getColumnIndex(TOTAL_LONG)));
					info.setTotalTime(c.getString(c.getColumnIndex(TOTAL_TIME)));
					info.setTrackName(c.getString(c.getColumnIndex(TRACK_NAME)));
					info.setTrackNote(c.getString(c.getColumnIndex(TRACK_NOTE)));
					lstTime.add(info);
				} while (c.moveToNext());
			}
			mLog.info(TIME_STOP_LIST + " getAllTimeStop Success");
			c.close();
		} catch (Exception e) {
			e.printStackTrace();
			mLog.error(TIME_STOP_LIST + " getAllTimeStop fail", e);
		}
		return lstTime;
	}

	// Anhpt67 edit xoa diem theo toa do
	public int detelePointByLatLng(String lat){
		int result = 0;
		//lat_=Double.parseDouble(lat);
		try {
			SQLiteDatabase db = this.getReadableDatabase();
			db.delete(POSITION_LIST,LAT + " =? ", new String[]{String.valueOf(lat)});
			result =1;
			
			/*Log.e("apt", "result---------------0");
			String selectQuery = "select time_stop_id from " + POSITION_LIST + " where latitude =" +lat;
			Cursor c = db.rawQuery(selectQuery, null);
			Log.e("apt", "result---------------1"+ "Cursor: "+c);
			if (c != null){
				c.moveToFirst();
				result = c.getInt(3);
			} else {
				Log.e("apt", "result---------------2");
			}
			Log.e("apt", "result  "+ result);
			//c.getInt(0);
*/			
			db.close();
		} catch (Exception e) {
			result = 0;
			e.printStackTrace();
			Log.e("apt", "result---------------3");
			// TODO: handle exception
		}
		return result;
	}
	
	
	
	/*
	 * anhpt67 xoa list_time_top va list position theo id va timetopid
	 */
/*	public int deleteRoadByTimeStopId(int time_stop_id) {
		int result = 0;
		try {
			SQLiteDatabase db = this.getReadableDatabase();
			db.delete(POSITION_LIST, TIME_STOP_ID + "= ? ",
					new String[] { String.valueOf(time_stop_id) });
			
			result = 1;
			mLog.info(POSITION_LIST + " deleteRoadByTimeStopId Success");
			db.close();
		} catch (Exception e) {
			result = 0;
			e.printStackTrace();
			mLog.error(POLES_LIST + " deleteRoadByTimeStopId fail", e);
		}
		Log.e("Result Delete: ", String.valueOf(result));
		return result;
	}
	*/
	// anhpt67 xoa list_time_top
	public int deleteListTimeStopByid(int id) {
		int result = 0;
		try {
			SQLiteDatabase db = this.getReadableDatabase();
			db.delete(TIME_STOP_LIST, KEY_ID + "= ? ",
					new String[] { String.valueOf(id) });
			result = 1;
			mLog.info(POSITION_LIST + " deleteListTimeStopByid Success");
			db.close();
		} catch (Exception e) {
			result = 0;
			e.printStackTrace();
			mLog.error(POLES_LIST + " deleteListTimeStopByid fail", e);
		}
		Log.e("Result Delete: ", String.valueOf(result));
		return result;
	}
	
	/*
	 * hungnd40_start Xoa cot dien theo id
	 */

	public int onDeleteByColId(int id) {

		int result = 0;
		try {
			SQLiteDatabase db = this.getReadableDatabase();
			db.delete(POLES_LIST, KEY_ID + "= ? ",
					new String[] { String.valueOf(id) });
			result = 1;
			mLog.info(POLES_LIST + " onDeleteByColId Success");
			db.close();
		} catch (Exception e) {
			result = 0;
			e.printStackTrace();
			mLog.error(POLES_LIST + " onDeleteByColId fail", e);
		}
		Log.e("Result Delete: ", String.valueOf(result));
		return result;
	}

	/*
	 * hungnd40_start Lay het cac toa do theo time id
	 */

	public List<InfomationForm> getPositionByTime(int timeId) {
		List<InfomationForm> lstCol = new ArrayList<InfomationForm>();
		try {
			SQLiteDatabase db = this.getReadableDatabase();
			Cursor c = db.query(POSITION_LIST,
					new String[] { KEY_ID, LAT, LONG }, TIME_STOP_ID + " = ? ",
					new String[] { String.valueOf(timeId) }, null, null,
					CREATETIME, null);
			if (c.moveToFirst()) {
				do {
					InfomationForm info = new InfomationForm();
					info.setId(c.getInt(c.getColumnIndex("id")));
					info.setLatitude(c.getString(c.getColumnIndex("latitude")));
					info.setLongtitude(c.getString(c
							.getColumnIndex("longtitude")));
					lstCol.add(info);
				} while (c.moveToNext());
			}
			mLog.info(POSITION_LIST + " getPositionByTime Success");
			c.close();
			db.close();
		} catch (Exception e) {
			e.printStackTrace();
			mLog.error(POSITION_LIST + " getPositionByTime fail", e);
		}
		return lstCol;
	}

	/*
	 * hungnd40_start Lay het cac toa do theo date
	 */

	public List<InfomationForm> getPositionByDate(java.util.Date date) {
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
		String to_date = sdf.format(date);

		List<InfomationForm> lstCol = new ArrayList<InfomationForm>();
		try {
			SQLiteDatabase db = this.getReadableDatabase();
			Cursor c = db.query(POSITION_LIST,
					new String[] { KEY_ID, LAT, LONG }, "date(CREATETIME) = ?",
					new String[] { to_date }, null, null, CREATETIME, null);
			if (c.moveToFirst()) {
				do {
					InfomationForm info = new InfomationForm();
					info.setId(c.getInt(c.getColumnIndex("id")));
					info.setLatitude(c.getString(c.getColumnIndex("latitude")));
					info.setLongtitude(c.getString(c
							.getColumnIndex("longtitude")));
					lstCol.add(info);
				} while (c.moveToNext());
			}
			mLog.info(POSITION_LIST + " getPositionByTime Success");
			c.close();
			db.close();
		} catch (Exception e) {
			e.printStackTrace();
			mLog.error(POSITION_LIST + " getPositionByTime fail", e);
		}
		return lstCol;
	}

	/*
	 * hungnd40_start Lay het cac toa do nha theo time_id
	 */

	public List<InfomationForm> getHouseByTime(int timeId) {
		List<InfomationForm> lstCol = new ArrayList<InfomationForm>();
		try {
			SQLiteDatabase db = this.getReadableDatabase();
			Cursor c = db.query(HOUSE_LIST, new String[] { KEY_ID, LAT, LONG },
					TIME_STOP_ID + " = ? ",
					new String[] { String.valueOf(timeId) }, null, null,
					KEY_ID, null);
			if (c.moveToFirst()) {
				do {
					InfomationForm info = new InfomationForm();
					info.setId(c.getInt(c.getColumnIndex("id")));
					info.setLatitude(c.getString(c.getColumnIndex("latitude")));
					info.setLongtitude(c.getString(c
							.getColumnIndex("longtitude")));
					lstCol.add(info);
				} while (c.moveToNext());
			}
			mLog.info(HOUSE_LIST + " getHouseByTime Success");
			c.close();
			db.close();
		} catch (Exception e) {
			e.printStackTrace();
			mLog.error(HOUSE_LIST + " getHouseByTime fail", e);
		}
		return lstCol;
	}

	/*
	 * hungnd40_start Lay het cac toa do nha theo date
	 */

	public List<InfomationForm> getHouseByDate(java.util.Date date) {
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
		String to_date = sdf.format(date);
		Log.e("dataaa", "todate="+ to_date);
		List<InfomationForm> lstCol = new ArrayList<InfomationForm>();
		try {
			SQLiteDatabase db = this.getReadableDatabase();
			Cursor c = db.query(HOUSE_LIST, new String[] { KEY_ID, LAT, LONG },
					"date(CREATETIME) = ?", new String[] { to_date }, null,
					null, KEY_ID, null);
			if (c.moveToFirst()) {
				do {
					InfomationForm info = new InfomationForm();
					info.setId(c.getInt(c.getColumnIndex("id")));
					info.setLatitude(c.getString(c.getColumnIndex("latitude")));
					info.setLongtitude(c.getString(c
							.getColumnIndex("longtitude")));
					lstCol.add(info);
				} while (c.moveToNext());
			}
			mLog.info(HOUSE_LIST + " getHouseByTime Success");
			c.close();
			db.close();
		} catch (Exception e) {
			e.printStackTrace();
			mLog.error(HOUSE_LIST + " getHouseByTime fail", e);
		}
		return lstCol;
	}

	/*
	 * hungnd40_start Lay het cac dia danh theo time_id
	 */

	public List<InfomationForm> getPlaceByTime(int timeId) {
		List<InfomationForm> lstCol = new ArrayList<InfomationForm>();
		try {
			SQLiteDatabase db = this.getReadableDatabase();
			Cursor c = db.query(PLACE_LIST, new String[] { KEY_ID, LAT, LONG },
					TIME_STOP_ID + " = ? ",
					new String[] { String.valueOf(timeId) }, null, null,
					KEY_ID, null);
			if (c.moveToFirst()) {
				do {
					InfomationForm info = new InfomationForm();
					info.setId(c.getInt(c.getColumnIndex("id")));
					info.setLatitude(c.getString(c.getColumnIndex("latitude")));
					info.setLongtitude(c.getString(c
							.getColumnIndex("longtitude")));
					lstCol.add(info);
				} while (c.moveToNext());
			}
			mLog.info(PLACE_LIST + " getPlaceByTime Success");
			c.close();
			db.close();
		} catch (Exception e) {
			e.printStackTrace();
			mLog.error(PLACE_LIST + " getPlaceByTime fail", e);
		}
		return lstCol;
	}

	/*
	 * hungnd40_start Lay het cac dia danh theo date
	 */

	public List<InfomationForm> getPlaceByDate(java.util.Date date) {
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
		String to_date = sdf.format(date);

		List<InfomationForm> lstCol = new ArrayList<InfomationForm>();
		try {
			SQLiteDatabase db = this.getReadableDatabase();
			Cursor c = db.query(PLACE_LIST, new String[] { KEY_ID, LAT, LONG },
					"date(CREATETIME) = ?", new String[] { to_date }, null,
					null, KEY_ID, null);
			if (c.moveToFirst()) {
				do {
					InfomationForm info = new InfomationForm();
					info.setId(c.getInt(c.getColumnIndex("id")));
					info.setLatitude(c.getString(c.getColumnIndex("latitude")));
					info.setLongtitude(c.getString(c
							.getColumnIndex("longtitude")));
					lstCol.add(info);
				} while (c.moveToNext());
			}
			mLog.info(PLACE_LIST + " getPlaceByTime Success");
			c.close();
			db.close();
		} catch (Exception e) {
			e.printStackTrace();
			mLog.error(PLACE_LIST + " getPlaceByTime fail", e);
		}
		return lstCol;
	}

	/*
	 * hungnd40_start Lay het cac toa do nha
	 */

	public List<InfomationForm> getAllHouse() {
		List<InfomationForm> lstTime = new ArrayList<InfomationForm>();
		try {
			SQLiteDatabase db = this.getReadableDatabase();
			Cursor c = db.query(HOUSE_LIST, new String[] { KEY_ID, LAT, LONG },
					null, null, null, null, KEY_ID, null);
			// looping through all rows and adding to list
			if (c.moveToFirst()) {
				do {
					InfomationForm info = new InfomationForm();
					info.setId(c.getInt(c.getColumnIndex(KEY_ID)));
					info.setLatitude(c.getString(c.getColumnIndex(LAT)));
					info.setLongtitude(c.getString(c.getColumnIndex(LONG)));
					lstTime.add(info);
				} while (c.moveToNext());
			}
			mLog.info(HOUSE_LIST + " getAllHouse Success");
			c.close();
		} catch (Exception e) {
			e.printStackTrace();
			mLog.error(HOUSE_LIST + " getAllHouse fail", e);
		}
		return lstTime;
	}

	public List<InfomationForm> getHouseNotInserted() {
		List<InfomationForm> lstTime = new ArrayList<InfomationForm>();
		try {
			SQLiteDatabase db = this.getReadableDatabase();
			Cursor c = db.query(HOUSE_LIST, new String[] { KEY_ID, LAT, LONG },
					" COMMITED is NULL"
					, null, null, null, CREATETIME + " DESC", null);
			// looping through all rows and adding to list
			if (c.moveToFirst()) {
				do {
					InfomationForm info = new InfomationForm();
					info.setId(c.getInt(c.getColumnIndex(KEY_ID)));
					info.setLatitude(c.getString(c.getColumnIndex(LAT)));
					info.setLongtitude(c.getString(c.getColumnIndex(LONG)));
					lstTime.add(info);
				} while (c.moveToNext());
			}
			mLog.info(HOUSE_LIST + " getAllHouse Success");
			c.close();
		} catch (Exception e) {
			e.printStackTrace();
			mLog.error(HOUSE_LIST + " getAllHouse fail", e);
		}
		return lstTime;
	}

	/*
	 * hungnd40_start Lay het cac dia danh
	 */

	public List<InfomationForm> getAllPlace() {
		List<InfomationForm> lstTime = new ArrayList<InfomationForm>();
		try {
			SQLiteDatabase db = this.getReadableDatabase();
			Cursor c = db.query(PLACE_LIST, new String[] { KEY_ID, LAT, LONG,
					PLACE_NAME, PLACE_NOTE }, null, null, null, null, KEY_ID,
					null);
			// looping through all rows and adding to list
			if (c.moveToFirst()) {
				do {
					InfomationForm info = new InfomationForm();
					info.setId(c.getInt(c.getColumnIndex(KEY_ID)));
					info.setLatitude(c.getString(c.getColumnIndex(LAT)));
					info.setLongtitude(c.getString(c.getColumnIndex(LONG)));
					info.setPlaceName(c.getString(c.getColumnIndex(PLACE_NAME)));
					info.setPlaceNote(c.getString(c.getColumnIndex(PLACE_NOTE)));
					lstTime.add(info);
				} while (c.moveToNext());
			}
			c.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return lstTime;
	}

	public List<InfomationForm> getPlaceNotInserted() {
		List<InfomationForm> lstTime = new ArrayList<InfomationForm>();
		try {
			SQLiteDatabase db = this.getReadableDatabase();
			Cursor c = db.query(PLACE_LIST, new String[] { KEY_ID, LAT, LONG,
					PLACE_NAME, PLACE_NOTE },
					" COMMITED is NULL"
					, null, null, null, CREATETIME + " DESC", null);
			// looping through all rows and adding to list
			if (c.moveToFirst()) {
				do {
					InfomationForm info = new InfomationForm();
					info.setId(c.getInt(c.getColumnIndex(KEY_ID)));
					info.setLatitude(c.getString(c.getColumnIndex(LAT)));
					info.setLongtitude(c.getString(c.getColumnIndex(LONG)));
					info.setPlaceName(c.getString(c.getColumnIndex(PLACE_NAME)));
					info.setPlaceNote(c.getString(c.getColumnIndex(PLACE_NOTE)));
					lstTime.add(info);
				} while (c.moveToNext());
			}
			c.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return lstTime;
	}

	public List<InfomationForm> getAllPositionNotInserted() {
		List<InfomationForm> lstTime = new ArrayList<InfomationForm>();
		try {
			SQLiteDatabase db = this.getReadableDatabase();
			Cursor c = db.query(POSITION_LIST, new String[] { KEY_ID, LAT,
					LONG, TIME_STOP_ID },
					" COMMITED is NULL"
					, null, null, null, CREATETIME + " DESC", null);
			// looping through all rows and adding to list
			if (c.moveToFirst()) {
				do {
					InfomationForm info = new InfomationForm();
					info.setId(c.getInt(c.getColumnIndex(KEY_ID)));
					info.setLatitude(c.getString(c.getColumnIndex(LAT)));
					info.setLongtitude(c.getString(c.getColumnIndex(LONG)));
					info.setTimeListId(c.getInt(c.getColumnIndex(TIME_STOP_ID)));
					lstTime.add(info);
				} while (c.moveToNext());
			}
			mLog.info(POSITION_LIST + " getAllPosition Success");
			c.close();
		} catch (Exception e) {
			e.printStackTrace();
			mLog.error(POSITION_LIST + " getAllPosition fail", e);
		}
		return lstTime;
	}
	
	/*
	 * hungnd40_start Lay het cac toa do
	 */

	public List<InfomationForm> getAllPosition() {
		List<InfomationForm> lstTime = new ArrayList<InfomationForm>();
		try {
			SQLiteDatabase db = this.getReadableDatabase();
			Cursor c = db.query(POSITION_LIST, new String[] { KEY_ID, LAT,
					LONG, TIME_STOP_ID }, null, null, null, null, CREATETIME,
					null);
			// looping through all rows and adding to list
			if (c.moveToFirst()) {
				do {
					InfomationForm info = new InfomationForm();
					info.setId(c.getInt(c.getColumnIndex(KEY_ID)));
					info.setLatitude(c.getString(c.getColumnIndex(LAT)));
					info.setLongtitude(c.getString(c.getColumnIndex(LONG)));
					info.setTimeListId(c.getInt(c.getColumnIndex(TIME_STOP_ID)));
					lstTime.add(info);
				} while (c.moveToNext());
			}
			mLog.info(POSITION_LIST + " getAllPosition Success");
			c.close();
		} catch (Exception e) {
			e.printStackTrace();
			mLog.error(POSITION_LIST + " getAllPosition fail", e);
		}
		return lstTime;
	}

	/*
	 * hungnd40_start Xoa du lieu theo ten bang
	 */

	public void deleteAllDB(String table) {
		SQLiteDatabase db = this.getWritableDatabase();
		try {
			db.delete(table, null, null);
			Log.e("Result + Delete", "Success");
			mLog.info(table + " Delete Success");
		} catch (Exception e) {
			e.printStackTrace();
			mLog.error(table + " deleteAllDB fail", e);
		}
	}

	public void updateInsertedData(String table) {
		SQLiteDatabase db = this.getWritableDatabase();
		try {
			ContentValues args = new ContentValues();
			args.put("COMMITED", 1);
			db.update(table, args, null, null);
			Log.e("Result + Update", "Success");
			mLog.info(table + " Update Success");
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	/*
	 * hungnd40_start Xoa theo id va ten bang
	 */
	public int deleteTableById(int id, String tableName) {

		int result = 0;
		try {
			SQLiteDatabase db = this.getReadableDatabase();
			db.delete(tableName, KEY_ID + "= ? ",
					new String[] { String.valueOf(id) });
			result = 1;
			db.close();
			Log.e("DELETE", "Success");
		} catch (Exception e) {
			Log.e("DELETE", "Fail");
			result = 0;
			e.printStackTrace();
		}
		Log.i("Result Delete: ", "DELETE TABLE: " + tableName + " || RESULT: "
				+ String.valueOf(result));
		return result;
	}
	
	public String getPlaceNameById(int id) {
		String name = null;
		SQLiteDatabase db = this.getReadableDatabase();
		String sql =  "select * from " + PLACE_LIST + " where " + KEY_ID + " = ?";
		try {
			Cursor cursor =  db.rawQuery(sql, new String[]{ String.valueOf(id) });
			if (cursor != null) {
	             if (cursor.moveToFirst()) {
	            	 	name = cursor.getString(cursor.getColumnIndex("place_name"));
	             }
	             cursor.close();
            }
		} catch (Exception e) {
			e.printStackTrace();
		}
		return name;
	}
	
	public String getPoleCodeById(int id) {
		String name = null;
		SQLiteDatabase db = this.getReadableDatabase();
		String sql =  "select * from " + POLES_LIST + " where " + KEY_ID + " = ?";
		try {
			Cursor cursor =  db.rawQuery(sql, new String[]{ String.valueOf(id) });
			if (cursor != null) {
	             if (cursor.moveToFirst()) {
	            	 	name = cursor.getString(cursor.getColumnIndex("col_code"));
	             }
	             cursor.close();
            }
		} catch (Exception e) {
			e.printStackTrace();
		}
		return name;
	}

	/*
	 * hungnd40_start Xoa theo id time va ten bang
	 */
	public int onDeleteByIdTimeTable(int idTime, String tableName) {

		int result = 0;
		try {
			SQLiteDatabase db = this.getReadableDatabase();
			db.delete(tableName, TIME_STOP_ID + "= ? ",
					new String[] { String.valueOf(idTime) });
			result = 1;
			db.close();
		} catch (Exception e) {
			result = 0;
			e.printStackTrace();
		}
		Log.e("Result Delete: ", String.valueOf(result));
		return result;
	}

	public int updateObjectById(int id, String latitude, String longitude,
			String tableName) {
		int result = 0;
		SQLiteDatabase db = this.getWritableDatabase();
		ContentValues values = new ContentValues();
		values.put(LONG, longitude);
		values.put(LAT, latitude);
		// update row
		try {
			db.update(tableName, values, KEY_ID + "= ? ",
					new String[] { String.valueOf(id) });
			result = 1;
			Log.e("UPDATE", "Success");
			db.close();
		} catch (Exception e) {
			result = 0;
			Log.e("UPDATE", "Fail");
			e.printStackTrace();
		}
		Log.i("Result Update: ", "UPDATE TABLE: " + tableName + " || RESULT: "
				+ String.valueOf(result));
		return result;

	}
	
	
	public int updateCommitedStatus(String tableName) {
		int result = 0;
		SQLiteDatabase db = this.getWritableDatabase();
		ContentValues values = new ContentValues();
		values.put("COMMITED", 1);
		try {
			db.update(tableName, values, " COMMITED is NULL", null);
			result = 1;
			Log.e("UPDATE", "Success");
			db.close();
		} catch (Exception e) {
			result = 0;
			Log.e("UPDATE", "Fail");
			e.printStackTrace();
		}
		Log.i("Result Update: ", "UPDATE TABLE: " + tableName + " || RESULT: "
				+ String.valueOf(result));
		return result;

	}
}